PRIMARY
KEY (PK):
A column or columns is called primary key (PK) that uniquely identifies each row in table. If we want to create a primary key, we should defines a PRIMARY KEY constraints when we create or modify a table. When we are working with multiple columns are used as a primary key, it is known as a composite primary key.
In creation time of composite primary key, we should use a few column as possible. It is good for storage and performance both, the more columns we use for primary key the more storage space you require.
If we are using primary key in my tables that must be follow a following
condition:
1.
It enforces the entity integrity of the table.
2.
It always has unique data
3.
Key length can’t exceeded than 900 bytes.
4.
Key can‘t have a null value.
5.
There can be no duplicate value for a primary
key.
6. A table can contain only one primary key constraints.
In internally, when we are creating a primary key constraints for a table, database engine has create automatically a unique index for the primary key. The main advantage of creating a primary key is that we get fast access with uniqueness.
Syntax of Creating a Primary key in Single Column:
Table creation time:
CREATE TABLE Employee(Employee_Id int NOT NULL PRIMARY KEY,Employee_Full_Name varchar (255) NOT NULL,Employee_Address varchar (255),Employee_City varchar (255))
After Table Creation:
If we are not defining a primary key
in creation time of table .So, we will define a primary key constraints after
table creation, using following syntax:
ALTER TABLE EmployeeADD PRIMARY KEY (Employee_Full_Name)
Syntax of Creating a Primary key in Multiple Column:
Table creation time:
CREATE TABLE Employee(Employee_Id int NOT NULL,Employee_Full_Name varchar (255) NOT NULL, Employee_Address varchar (255), Employee_City varchar (255) CONSTRAINTS pk_EmployeeID PRIMARY KEY (Employee_Id, Employee_Full_Name) )
If we want to drop a primary key constraints in my tables
then we will use following syntax:
ALTER TABLE EmployeeDROP CONSTRAINTS pk_EmployeeID
FOREIGN KEY:
In RDBMS, a foreign key is a field or a column i.e. used to
establish a link between two tables. Or we say simply, a foreign key in one
table are using a primary key in another table.
Now, we are explain with example. Here, we have two tables
of Employee as follows:
Employee_ID |
FirstName |
LastName |
City |
001 |
ANUPAM |
MISHRA |
DELHI |
002 |
ANKUSH |
SONI |
MURADABAD |
003 |
AKHILESH |
ANAND |
LUCKNOW |
004 |
AMRISH |
SHAH |
KANPUR |
Employee_Dep_ID |
Department |
Employee_ID |
1001 |
Computer Science |
1 |
1002 |
Electrical |
2 |
1003 |
Civil |
1 |
Here “Employee_ID”
column is the “Employee” table is
the primary key in the “Employee” table. The “Employee_ID” column in “Department”
table is a foreign key in Department
table.
We have also define a foreign key creation time of the table
“Department”(we have taken as above)
CREATE TABLE Department(Emp_Dep_ID int NOT NULL PRIMARY KEY,Department varchar (255) NOT NULL,FOREIGN KEY (Employee_ID)REFERENCES Employee(Employee_ID))
ALTER TABLE DepartmentADD CONSTRAINTS fk_DepartmentsFOREIGN KEY (Employee_ID) REFERENCES Employee (Employee_ID)
Drop Syntax for FOREIGN KEY CONSTRAINTS:
Alter table DepartmentDROP CONSTRAINTS fk_Departments
Here some important differences between primary key and foreign key in sql.
1. Foreign key is a null but primary key is not.
2. Foreign key can be duplicate but primary key is
not.
3. By default primary key adds a clustered index
but a foreign key does not automatically create an index, clustered or non-clustered.
We must manually create an index for foreign key.
4. There is an only one primary key in a table but we can have more than one foreign key.
Composite Key:
A composite key is a combination of two or more columns in a table that can be used to uniquely identify each row in the table when the columns are combined uniqueness is guarantee, but when it taken individually it does not guarantee uniqueness.
Sometimes more than one
attributes are needed to uniquely identify an entity. A primary key that is made by the combination of more
than one attribute is known as a composite key.
In other words we can
say that:
Composite key is a
key which is the combination of more than one field or column of a given table.
It may be a candidate key or primary key.
Columns
that make up the composite key can be of different data types.
Syntax:
CREATE TABLE table_name(Column1 datatype,Column2 datatype,??????PRIMARY KEY (Column1, Column2))
Here, all cases composite key created consist of column1 and
column2.
UNIQUE KEY:
We can say it is a little like a primary key. If we want to
define uniqueness a single field/column or more than one in database table, we
using a UNIQUE KEY .We have defines more than one unique keys in a table but
only one primary key.
For example we are taking a above “Employee” table and
defines a UNIQUE Key instead of Primary key.
CREATE TABLE Employee(Employee_ID int NOT NULL UNIQUEEmployee_Full_Name varchar(255),Employee_City varchar(255))
ALTERNATE KEY:
If we are defining more than one candidate key in a table and rest columns there are one is primary key and other is called an alternate key. We can also say, an alternate key, the column may not be primary key but still it is a unique key in the column. For example, we are taken an “Employee” table but Employee_ID is a primary key and rest one is a candidate key.
Leave Comment